﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;

namespace ExcelHelper
{
    /// <summary>    
    /// 标题：将 DataGridView 导出到 Excel 或 Xml 通用类    
    /// 描述：导出时可以指定需要导出的 DataGridViewColumn 及自定义导出后的列名    
    /// </summary>    
    public class ExportHelper {
        private object missing = Type.Missing;

        #region " DataGridViewToExcel "    

        /// <summary>    
        /// 将 DataGridView 保存为 Excel    
        /// </summary>    
        /// <param name="dataGridView">DataGridView 对象</param>    
        /// <param name="filePath">保存的文件路径</param>    
        public void DataGridViewToExcel(DataGridView dataGridView,String filePath) {
            DataGridViewToExcel(new DataGridViewExportOptions(dataGridView),filePath);
        }
        /// <summary>    
        /// 将 DataGridView 保存为 Excel    
        /// </summary>    
        /// <param name="dataGridViewExportOption">DataGridViewExportOptions 对象</param>    
        /// <param name="filePath">保存的文件路径</param>    
        public void DataGridViewToExcel(DataGridViewExportOptions dataGridViewExportOption,String filePath) {
            DataGridViewToExcel(new List<DataGridViewExportOptions>(new DataGridViewExportOptions[] { dataGridViewExportOption }),filePath);
        }
        /// <summary>    
        /// 将 DataGridView 保存为 Excel    
        /// </summary>    
        /// <param name="dataGridViewExportOptions">DataGridViewExportOptions 对象列表</param>    
        /// <param name="filePath">保存的文件路径</param>    
        public void DataGridViewToExcel(List<DataGridViewExportOptions> dataGridViewExportOptions,String filePath) {
            if(dataGridViewExportOptions == null || dataGridViewExportOptions.Count == 0)
                return;

            #region " 判断文件是否存在，存在则删除原有文件 "    
            try {
                if(File.Exists(filePath))
                    File.Delete(filePath);
            } catch {
                return;
            }
            #endregion

            Excel.Application application = new Excel.Application();
            application.Visible = false;
            application.UserControl = false;

            Excel.Workbook workBook = (Excel.Workbook)(application.Workbooks.Add(missing));

            #region " 根据需要导出的 DataGridView 数量，预先增加不足的工作表或移除多余的工作表 "    
            // 移除多余的工作表    
            while(application.ActiveWorkbook.Sheets.Count > dataGridViewExportOptions.Count)
                ((Excel.Worksheet)application.ActiveWorkbook.Sheets[1]).Delete();
            // 添加工作表    
            while(application.ActiveWorkbook.Sheets.Count < dataGridViewExportOptions.Count)
                application.Worksheets.Add(missing,missing,missing,missing);
            #endregion

            int sheetIndex = 1;
            List<String> sheetNames = new List<String>();
            try {
                foreach(DataGridViewExportOptions option in dataGridViewExportOptions) {
                    #region " 处理在多个 DataGridView 设置为相同的工作表名称的问题 "    
                    if(sheetNames.Contains(option.WorkSheetName)) {
                        int i = 1;
                        while(true) {
                            string newSheetName = option.WorkSheetName + i.ToString();
                            if(!sheetNames.Contains(newSheetName)) {
                                sheetNames.Add(newSheetName);
                                option.WorkSheetName = newSheetName;
                                break;
                            }
                            i++;
                        }
                    } else {
                        sheetNames.Add(option.WorkSheetName);
                    }
                    #endregion

                    ExportToExcel(application,workBook,(Excel.Worksheet)application.ActiveWorkbook.Sheets[sheetIndex],option);
                    sheetIndex++;
                }
                workBook.SaveAs(filePath,missing,missing,missing,missing,missing,Excel.XlSaveAsAccessMode.xlExclusive,missing,missing,missing,missing,missing);
            } finally {
                application.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(application);
                GC.Collect();
            }
        }

        #endregion

        #region " DataGridViewToXml "    

        public void DataGridViewToXml(DataGridView dataGridView,String filePath) {
            DataGridViewToXml(new DataGridViewExportOptions(dataGridView),filePath);
        }

        public void DataGridViewToXml(DataGridViewExportOptions dataGridViewExportOption,String filePath) {
            DataGridViewToXml(new List<DataGridViewExportOptions>(new DataGridViewExportOptions[] { dataGridViewExportOption }),filePath);
        }

        public void DataGridViewToXml(List<DataGridViewExportOptions> dataGridViewExportOptions,String filePath) {
            if(dataGridViewExportOptions == null || dataGridViewExportOptions.Count == 0)
                return;

            DataSet dataSet = new DataSet();

            int sheetIndex = 1;
            List<String> sheetNames = new List<String>();
            try {
                foreach(DataGridViewExportOptions option in dataGridViewExportOptions) {
                    #region " 处理在多个 DataGridView 设置为相同的表名称的问题 "   
                    if(sheetNames.Contains(option.WorkSheetName)) {
                        int i = 1;
                        while(true) {
                            string newSheetName = option.WorkSheetName + i.ToString();
                            if(!sheetNames.Contains(newSheetName)) {
                                sheetNames.Add(newSheetName);
                                option.WorkSheetName = newSheetName;
                                break;
                            }
                            i++;
                        }
                    } else {
                        sheetNames.Add(option.WorkSheetName);
                    }
                    DataGridViewFillToDataSet(dataSet,option);
                    sheetIndex++;
                    #endregion
                }
                ExportToXml(dataSet,filePath);
            } finally {
                dataSet.Dispose();
                GC.Collect();
            }
        }

        // 处理 DataGridView 中的数据以填充到指定的 DataSet 中    
        private void DataGridViewFillToDataSet(DataSet dataSet,DataGridViewExportOptions Option) {
            DataTable Table = new DataTable();
            Table.TableName = Option.WorkSheetName;

            if(Option.DataGridView == null)
                return;

            #region " 填充表头内容 "    

            foreach(DataColumnExportOptions option in Option.VisibleColumnOptions) {
                if(!option.Visible)
                    continue;
                Table.Columns.Add(new DataColumn(option.ColumnName));
            }

            #endregion

            #region " 填充表格正文内容 "    

            foreach(DataGridViewRow dataRow in Option.DataGridView.Rows) {
                if(dataRow.IsNewRow)
                    continue;

                DataRow Row = Table.NewRow();
                foreach(DataColumnExportOptions option in Option.VisibleColumnOptions) {
                    if(dataRow.Cells[option.ColumnName].Value == null) {
                        Row[option.ColumnName] = "";
                    } else {
                        Row[option.ColumnName] = dataRow.Cells[option.ColumnName].Value.ToString();
                    }
                }
                Table.Rows.Add(Row);
            }

            #endregion
            dataSet.Tables.Add(Table);
        }

        #endregion

        #region " ExportToXml "    
        /// <summary>    
        /// 保存 DataSet 数据到 Xml 文件    
        /// </summary>    
        /// <param name="dataSet">DataSet数据对象</param>    
        /// <param name="filePath">Xml 文件地址</param>    
        private void ExportToXml(DataSet dataSet,String filePath) {
            #region " 判断文件是否存在，存在则删除原有文件 "    
            try {
                if(File.Exists(filePath))
                    File.Delete(filePath);
            } catch {
                return;
            }
            #endregion

            dataSet.WriteXml(filePath);
        }
        #endregion

        #region " ExportToExcel "    

        private void ExportToExcel(Excel.Application application,Excel._Workbook workBook,Excel.Worksheet worksheet,DataGridViewExportOptions Option) {
            Excel.Range range;
            worksheet.Name = Option.WorkSheetName;

            if(Option.DataGridView == null)
                return;

            int _RowCount = Option.DataGridView.AllowUserToAddRows ? Option.DataGridView.Rows.Count - 1 : Option.DataGridView.Rows.Count;
            int _CellCount = Option.VisibleColumnOptions.Count;
            int _CellIndex = 0;
            int _RowIndex = 0;

            #region " 填充表头内容 "    

            object[,] _HeadValues = new object[1,_CellCount];

            foreach(DataColumnExportOptions option in Option.VisibleColumnOptions) {
                if(!option.Visible)
                    continue;
                _HeadValues[0,_CellIndex] = option.Caption;
                _CellIndex++;
            }
            range = worksheet.get_Range(GetExcelCellName(1,1),GetExcelCellName(_CellCount,1));
            range.Value2 = _HeadValues;

            #endregion

            #region " 设置表头样式 "    

            range.Font.Bold = true;
            range.Font.Name = "Georgia";
            range.Font.Size = 10;
            range.RowHeight = 26;
            range.EntireColumn.AutoFit();

            #endregion

            #region " 填充表格正文内容 "    

            object[,] _RowValues = new object[_RowCount,_CellCount];

            _RowIndex = 0;

            foreach(DataGridViewRow dataRow in Option.DataGridView.Rows) {
                if(dataRow.IsNewRow)
                    continue;
                _CellIndex = 0;
                foreach(DataColumnExportOptions option in Option.VisibleColumnOptions) {
                    _RowValues[_RowIndex,_CellIndex] = dataRow.Cells[option.ColumnName].Value;
                    _CellIndex++;
                }
                _RowIndex++;
            }

            range = worksheet.get_Range(GetExcelCellName(1,2),GetExcelCellName(_CellCount,_RowCount + 1));
            range.Value2 = _RowValues;

            #endregion

            #region " 设置表格正文样式 "    

            range.Font.Name = "Georgia";
            range.Font.Size = 9;
            range.RowHeight = 18;
            range.EntireColumn.AutoFit();

            #endregion

            #region " 设置单元格边框样式 "    

            range = worksheet.get_Range(GetExcelCellName(1,1),GetExcelCellName(_CellCount,_RowCount + 1));
            range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
            range.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
            range.Borders.Weight = Excel.XlBorderWeight.xlThin;
            range.Borders.Color = Color.Black.ToArgb();

            #endregion
        }

        #endregion

        #region " GetExcelCellName "    

        /// <summary>    
        /// 取得Excel单元格编号    
        /// </summary>    
        /// <param name="fColIndex"></param>    
        /// <param name="fRowIndex"></param>    
        /// <returns>A1,A2,B3,...,AB10,...</returns>    
        private string GetExcelCellName(int cellIndex,int rowIndex) {
            if(cellIndex <= 0 || cellIndex > 256) {
                throw new Exception("Excel 列索引数值超出范围(1-256)!");
            } else if(cellIndex <= 26) {
                return GetExcelCellName(cellIndex) + rowIndex.ToString();
            } else {
                string retLetter = GetExcelCellName(cellIndex / 26);
                retLetter += GetExcelCellName(cellIndex % 26);
                retLetter += rowIndex.ToString();
                return retLetter;
            }
        }
        /// <summary>    
        /// 取得Excel单元格列编号    
        /// </summary>    
        /// <param name="cellIndex"></param>    
        /// <returns>A,B,...,AC,...</returns>    
        private string GetExcelCellName(int cellIndex) {
            int i = 1;
            foreach(string letter in Enum.GetNames(typeof(ExcelColumnLetters))) {
                if(i == cellIndex)
                    return letter;
                i++;
            }
            throw new Exception("Excel 列索引数值超出范围(1-256)!");
        }

        #endregion
    }

    #region " ExcelColumnLetters "    
    enum ExcelColumnLetters {
        A = 1, B = 2, C = 3, D = 4, E = 5, F = 6, G = 7, H = 8, I = 9, J = 10,
        K = 11, L = 12, M = 13, N = 14, O = 15, P = 16, Q = 17, R = 18, S = 19, T = 20,
        U = 21, V = 22, W = 23, X = 24, Y = 25, Z = 26
    }
    #endregion

    #region " DataColumnExportOptions "    
    /// <summary>    
    /// 导出数据字段属性选项类    
    /// </summary>    
    public class DataColumnExportOptions {
        private String _ColumnName;
        private String _Caption;
        private Boolean _Visible;
        /// <summary>    
        /// 字段名称    
        /// </summary>    
        public String ColumnName {
            get {
                return _ColumnName;
            }
            set {
                _ColumnName = value;
            }
        }
        /// <summary>    
        /// 字段标题    
        /// </summary>    
        public String Caption {
            get {
                return _Caption;
            }
            set {
                _Caption = value;
            }
        }
        /// <summary>    
        /// 是否显示（导出）    
        /// </summary>    
        public Boolean Visible {
            get {
                return _Visible;
            }
            set {
                _Visible = value;
            }
        }
        /// <summary>    
        /// 构造函数    
        /// </summary>    
        /// <param name="fColumnName">字段名称</param>    
        public DataColumnExportOptions(String columnName)
            : this(columnName,columnName) {

        }
        /// <summary>    
        /// 构造函数    
        /// </summary>    
        /// <param name="fColumnName">字段名称</param>    
        /// <param name="fCaption">字段标题</param>    
        public DataColumnExportOptions(String columnName,String caption)
            : this(columnName,caption,true) {

        }
        /// <summary>    
        /// 构造函数    
        /// </summary>    
        /// <param name="fColumnName">字段名称</param>    
        /// <param name="fCaption">字段标题</param>    
        /// <param name="fVisible">是否显示（导出）</param>    
        public DataColumnExportOptions(String columnName,String caption,Boolean visible) {
            this._ColumnName = columnName;
            this._Caption = caption;
            this._Visible = visible;
        }
    }
    #endregion

    #region " DataGridViewExportOptions “    

    public class DataGridViewExportOptions {
        private DataGridView _DataGridView;
        private List<DataColumnExportOptions> _ColumnOptions;
        private List<DataColumnExportOptions> _VisibleColumnOptions;
        private String _WorkSheetName;

        /// <summary>    
        /// 要导出到DataGridView对象    
        /// </summary>    
        public DataGridView DataGridView {
            get {
                return _DataGridView;
            }
            set {
                _DataGridView = value;
            }
        }
        /// <summary>    
        /// 导出的字段属性列表    
        /// </summary>    
        public List<DataColumnExportOptions> ColumnOptions {
            get {
                return _ColumnOptions;
            }
            set {
                _ColumnOptions = value;
            }
        }
        /// <summary>    
        /// 要导出的字段列表（只读）    
        /// </summary>    
        public List<DataColumnExportOptions> VisibleColumnOptions {
            get {
                return _VisibleColumnOptions;
            }
        }
        /// <summary>    
        /// 导出的工作表名称    
        /// </summary>    
        public String WorkSheetName {
            get {
                return _WorkSheetName;
            }
            set {
                _WorkSheetName = value;
            }
        }
        /// <summary>    
        /// 构造函数    
        /// </summary>    
        /// <param name="dataGridView">要导出到DataGridView对象</param>    
        public DataGridViewExportOptions(DataGridView dataGridView)
            : this(dataGridView,null) {
        }
        /// <summary>    
        /// 构造函数    
        /// </summary>    
        /// <param name="dataGridView">要导出到DataGridView对象</param>    
        /// <param name="columnOptions">导出的字段属性列表</param>    
        public DataGridViewExportOptions(DataGridView dataGridView,List<DataColumnExportOptions> columnOptions)
            : this(dataGridView,columnOptions,null) { }
        /// <summary>    
        /// 构造函数    
        /// </summary>    
        /// <param name="dataGridView">要导出到DataGridView对象</param>    
        /// <param name="columnOptions">导出的字段属性列表</param>    
        /// <param name="workSheetName">导出生成的工作表名称</param>    
        public DataGridViewExportOptions(DataGridView dataGridView,List<DataColumnExportOptions> columnOptions,String workSheetName) {
            if(dataGridView == null)
                return;

            this._DataGridView = dataGridView;
            if(columnOptions == null) {
                this._ColumnOptions = new List<DataColumnExportOptions>();
                foreach(DataGridViewColumn dataColumn in dataGridView.Columns)
                    this._ColumnOptions.Add(new DataColumnExportOptions(dataColumn.Name,dataColumn.HeaderText,dataColumn.Visible));
            } else {
                this._ColumnOptions = columnOptions;
            }

            if(String.IsNullOrEmpty(workSheetName))
                this._WorkSheetName = dataGridView.Name;
            else
                this._WorkSheetName = workSheetName;

            this._VisibleColumnOptions = new List<DataColumnExportOptions>();
            foreach(DataColumnExportOptions option in this._ColumnOptions) {
                if(option.Visible)
                    this._VisibleColumnOptions.Add(option);
            }
        }
    }

    #endregion
}
